package com.szgd.util;

import java.awt.image.BufferedImage;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLDecoder;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.szgd.bean.SysDict;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;


/* 
 * ExcelUtil工具类实现功能: 
 * 导出时传入list<T>,即可实现导出为一个excel,其中每个对象Ｔ为Excel中的一条记录. 
 * 导入时读取excel,得到的结果是一个list<T>.T是自己定义的对象. 
 * 需要导出的实体对象只需简单配置注解就能实现灵活导出,通过注解您可以方便实现下面功能: 
 * 1.实体属性配置了注解就能导出到excel中,每个属性都对应一列. 
 * 2.列名称可以通过注解配置. 
 * 3.导出到哪一列可以通过注解配置. 
 * 4.鼠标移动到该列时提示信息可以通过注解配置. 
 * 5.用注解设置只能下拉选择不能随意填写功能. 
 * 6.用注解设置是否只导出标题而不导出内容,这在导出内容作为模板以供用户填写时比较实用. 
 * 本工具类以后可能还会加功能,请关注我的博客: http://blog.csdn.net/lk_blog 
 */
public class ExcelUtil<T> {
	Class<T> clazz;


	public ExcelUtil(Class<T> clazz) {
		this.clazz = clazz;
	}

	/**
	 * 导入excel成对象
	 * @param clazz
	 * @param sheetName
	 * @param input
	 * @return
	 * @throws Exception 
	 */
	public List<T> importExcel(Class<T> clazz, String sheetName,
			InputStream input,String fileName) throws Exception {
		int maxCol = 0;
		List<T> list = new LinkedList<T>();
		Workbook workbook = getWorkbook(input, fileName);
		if (null == workbook) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		sheet = workbook.getSheet(sheetName);
		if (!sheetName.trim().equals("")) {
			sheet = workbook.getSheet(sheetName);// 如果指定sheet名,则取指定sheet中的内容.
		}
		if (sheet == null) {
			sheet = workbook.getSheetAt(0); // 如果传入的sheet名不存在则默认指向第1个sheet.
		}
		int rows = sheet.getPhysicalNumberOfRows();

		if (rows > 0) {// 有数据时才处理
			// Field[] allFields = clazz.getDeclaredFields();// 得到类的所有field.
			List<Field> allFields = getMappedFiled(clazz, null);

			Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();// 定义一个map用于存放列的序号和field.
			for (Field field : allFields) {
				// 将有注解的field存放到map中.
				if (field.isAnnotationPresent(ExcelVOAttribute.class)) {
					ExcelVOAttribute attr = field
							.getAnnotation(ExcelVOAttribute.class);
					int col = getExcelCol(attr.column());// 获得列号
					maxCol = Math.max(col, maxCol);
					// System.out.println(col + "====" + field.getName());
					field.setAccessible(true);// 设置类的私有字段属性可访问.
					fieldsMap.put(col, field);
				}
			}
			for (int i = 1; i < rows; i++) {// 从第2行开始取数据,默认第一行是表头.
				row = sheet.getRow(i);
				// int cellNum = row.getPhysicalNumberOfCells();
				// int cellNum = row.getLastCellNum();
				int cellNum = maxCol;
				T entity = null;
				for (int j = 0; j < cellNum; j++) {
					cell = row.getCell(j);
					if (cell == null) {
						continue;
					}
					int cellType = cell.getCellType();
					String c = "";
					if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
						c = String.valueOf(cell.getNumericCellValue());
					} else if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) {
						c = String.valueOf(cell.getBooleanCellValue());
					} else {
						c = cell.getStringCellValue();
					}
					if (c == null || c.equals("")) {
						continue;
					}
					entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建.
					// System.out.println(cells[j].getContents());
					Field field = fieldsMap.get(j);// 从map中得到对应列的field.
					if (field == null) {
						continue;
					}
					// 取得类型,并根据对象类型设置值.
					Class<?> fieldType = field.getType();
					if (String.class == fieldType) {
						field.set(entity, String.valueOf(c));
					} else if ((Integer.TYPE == fieldType)
							|| (Integer.class == fieldType)) {
						field.set(entity, Integer.parseInt(c));
					} else if ((Long.TYPE == fieldType)
							|| (Long.class == fieldType)) {
						field.set(entity, Long.valueOf(c));
					} else if ((Float.TYPE == fieldType)
							|| (Float.class == fieldType)) {
						field.set(entity, Float.valueOf(c));
					} else if ((Short.TYPE == fieldType)
							|| (Short.class == fieldType)) {
						field.set(entity, Short.valueOf(c));
					} else if ((Double.TYPE == fieldType)
							|| (Double.class == fieldType)) {
						field.set(entity, Double.valueOf(c));
					} else if (BigDecimal.class == fieldType) {
						field.set(entity, BigDecimal.valueOf(Double.valueOf(c)));
					}else if (Character.TYPE == fieldType) {
						if ((c != null) && (c.length() > 0)) {
							field.set(entity,
									Character.valueOf(c.charAt(0)));
						}
					}

				}
				if (entity != null) {
					list.add(entity);
				}
			}
		}
		return list;
	}
	
	/**
	 * 导入excel成对象（固资匹配用，其它时用共通导入）
	 * @param clazz
	 * @param sheetName
	 * @param input
	 * @return
	 * @throws Exception 
	 */
	public List<T> importExcelGzpp(Class<T> clazz, String sheetName,
			InputStream input,String fileName) throws Exception {
		int maxCol = 0;
		List<T> list = new LinkedList<T>();
		Workbook workbook = getWorkbook(input, fileName);
		if (null == workbook) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		sheet = workbook.getSheet(sheetName);
		if (!sheetName.trim().equals("")) {
			sheet = workbook.getSheet(sheetName);// 如果指定sheet名,则取指定sheet中的内容.
		}
		if (sheet == null) {
			sheet = workbook.getSheetAt(0); // 如果传入的sheet名不存在则默认指向第1个sheet.
		}
		int rows = sheet.getPhysicalNumberOfRows();

		if (rows > 0) {// 有数据时才处理
			// Field[] allFields = clazz.getDeclaredFields();// 得到类的所有field.
			List<Field> allFields = getMappedFiled(clazz, null);

			Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();// 定义一个map用于存放列的序号和field.
			for (Field field : allFields) {
				// 将有注解的field存放到map中.
				if (field.isAnnotationPresent(ExcelVOAttribute.class)) {
					ExcelVOAttribute attr = field
							.getAnnotation(ExcelVOAttribute.class);
					int col = getExcelCol(attr.column());// 获得列号
					maxCol = Math.max(col, maxCol);
					// System.out.println(col + "====" + field.getName());
					field.setAccessible(true);// 设置类的私有字段属性可访问.
					fieldsMap.put(col, field);
				}
			}
			for (int i = 1; i < rows; i++) {// 从第2行开始取数据,默认第一行是表头.
				row = sheet.getRow(i);
				// int cellNum = row.getPhysicalNumberOfCells();
				// int cellNum = row.getLastCellNum();
				int cellNum = maxCol;
				T entity = null;
				for (int j = 0; j < cellNum; j++) {
					cell = row.getCell(j);
					if (cell == null) {
						continue;
					}
					int cellType = cell.getCellType();
					String c = "";
					if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
						c = String.valueOf(cell.getNumericCellValue());
					} else if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) {
						c = String.valueOf(cell.getBooleanCellValue());
					} else {
						c = cell.getStringCellValue();
					}
					if (c == null || c.equals("")) {
						continue;
					}
					entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建.
					// System.out.println(cells[j].getContents());
					Field field = fieldsMap.get(j);// 从map中得到对应列的field.
					if (field == null) {
						continue;
					}
					// 取得类型,并根据对象类型设置值.
					Class<?> fieldType = field.getType();
					if (String.class == fieldType) {
						field.set(entity, String.valueOf(c));
					} else if ((Integer.TYPE == fieldType)
							|| (Integer.class == fieldType)) {
						field.set(entity, Integer.parseInt(c));
					} else if ((Long.TYPE == fieldType)
							|| (Long.class == fieldType)) {
						field.set(entity, Long.valueOf(c));
					} else if ((Float.TYPE == fieldType)
							|| (Float.class == fieldType)) {
						field.set(entity, Float.valueOf(c));
					} else if ((Short.TYPE == fieldType)
							|| (Short.class == fieldType)) {
						field.set(entity, Short.valueOf(c));
					} else if ((Double.TYPE == fieldType)
							|| (Double.class == fieldType)) {
						field.set(entity, Double.valueOf(c));
					} else if (BigDecimal.class == fieldType) {
						field.set(entity, BigDecimal.valueOf(Double.valueOf(c)));
					}else if (Character.TYPE == fieldType) {
						if ((c != null) && (c.length() > 0)) {
							field.set(entity,
									Character.valueOf(c.charAt(0)));
						}
					}

				}
				if (entity != null) {
					list.add(entity);
				}
			}
		}
		return list;
	}

	private static HSSFCellStyle getfieldNameStyle(HSSFWorkbook workbook){
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		//上下左右边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		//自动换行
		style.setWrapText(true);
		HSSFFont font = workbook.createFont();
		
		//第一行的样式
		//字体
		font.setFontName("宋体");
		//字号
		font.setFontHeightInPoints((short) 22);
		//加粗
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		return style;
	}
	
	private static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook){
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		style.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);
		//上下左右边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		//自动换行
		style.setWrapText(true);
		HSSFFont font = workbook.createFont();
		
		//第一行的样式
		//字体
		font.setFontName("宋体");
		//字号
		font.setFontHeightInPoints((short) 13);
		//加粗
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		return style;
	}
	
	private static  HSSFCellStyle getFieldStyle(HSSFWorkbook workbook){
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //上下左右边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //自动换行
        style.setWrapText(true);
        HSSFFont font = workbook.createFont();

        //第一行的样式
        //字体
        font.setFontName("宋体");

        //字号
        font.setFontHeightInPoints((short) 14);


        style.setFont(font);
        return style;
    }
	
	private static HSSFCellStyle getHeadStyle(HSSFWorkbook workbook){
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		//上下左右边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		//自动换行
		style.setWrapText(true);
		HSSFFont font = workbook.createFont();
		
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		return style;
	}
	
	private static HSSFCellStyle getDataStyle(HSSFWorkbook workbook){
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		//上下左右边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		//自动换行
		style.setWrapText(true);
		HSSFFont font = workbook.createFont();

		font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		style.setFont(font);
		style.setFillBackgroundColor(HSSFColor.WHITE.index);
		return style;
	}
	
	private static HSSFCellStyle getEndStyle(HSSFWorkbook workbook){
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		//上下左右边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);

		//自动换行
		style.setWrapText(true);
		HSSFFont font = workbook.createFont();

		font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		style.setFont(font);
		style.setFillBackgroundColor(HSSFColor.WHITE.index);
		return style;
	}
	

	
	private static HSSFCellStyle getHzsqfieldNameStyle(HSSFWorkbook workbook){
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		//上下左右边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		//自动换行
		style.setWrapText(true);
		HSSFFont font = workbook.createFont();

		//第一行的样式
		//字体
		font.setFontName("宋体");
		//字号
		font.setFontHeightInPoints((short) 16);
		//加粗
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		return style;
	}
	
	private static HSSFCellStyle getHzsqHeadLeftStyle(HSSFWorkbook workbook){
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		//上下左右边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		//自动换行
		style.setWrapText(true);
		HSSFFont font = workbook.createFont();
		
		//第一行的样式
		//字体
		font.setFontName("宋体");
		//字号
		font.setFontHeightInPoints((short) 12);
		//加粗
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		return style;
	}
	
	private static HSSFCellStyle getWxsqHeadLeftStyle(HSSFWorkbook workbook){
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		//上下左右边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		//自动换行
		style.setWrapText(true);
		HSSFFont font = workbook.createFont();
		
		//第一行的样式
		//字体
		font.setFontName("宋体");
		//字号
		font.setFontHeightInPoints((short) 12);
		//加粗
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		return style;
	}
	
	private static HSSFCellStyle getHzsqHeadLeftNoTopRightStyle(HSSFWorkbook workbook){
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		//上下左右边框
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		//自动换行
		style.setWrapText(true);
		HSSFFont font = workbook.createFont();
		
		//第一行的样式
		//字体
		font.setFontName("宋体");
		//字号
		font.setFontHeightInPoints((short) 12);
		//加粗
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		return style;
	}
	
	private static HSSFCellStyle getHzsqHeadCenterStyle(HSSFWorkbook workbook){
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		//上下左右边框
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		//自动换行
		style.setWrapText(true);
		HSSFFont font = workbook.createFont();
		
		//第一行的样式
		//字体
		font.setFontName("宋体");
		//字号
		font.setFontHeightInPoints((short) 12);
		//加粗
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		return style;
	}
	
	private static HSSFCellStyle getWxsqHeadCenterStyle(HSSFWorkbook workbook){
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		//上下左右边框
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		//自动换行
		style.setWrapText(true);
		HSSFFont font = workbook.createFont();
		
		//第一行的样式
		//字体
		font.setFontName("宋体");
		//字号
		font.setFontHeightInPoints((short) 12);
		//加粗
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		return style;
	}
	
	private static HSSFCellStyle getHzsqHeadRightStyle(HSSFWorkbook workbook){
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		//上下左右边框
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		//自动换行
		style.setWrapText(true);
		HSSFFont font = workbook.createFont();
		
		//第一行的样式
		//字体
		font.setFontName("宋体");
		//字号
		font.setFontHeightInPoints((short) 12);
		//加粗
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		return style;
	}
	
	private static HSSFCellStyle getWssqHeadRightStyle(HSSFWorkbook workbook){
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		//上下左右边框
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		//自动换行
		style.setWrapText(true);
		HSSFFont font = workbook.createFont();
		
		//第一行的样式
		//字体
		font.setFontName("宋体");
		//字号
		font.setFontHeightInPoints((short) 12);
		//加粗
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		return style;
	}
	
	private static HSSFCellStyle getHzsqDataStyle(HSSFWorkbook workbook){
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
		//上下左右边框
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		//自动换行
		style.setWrapText(true);
		HSSFFont font = workbook.createFont();

		font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		style.setFont(font);
		style.setFillBackgroundColor(HSSFColor.WHITE.index);
		return style;
	}
	
	private static HSSFCellStyle getHzsqDataRightStyle(HSSFWorkbook workbook){
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);
		//自动换行
		style.setWrapText(true);
		HSSFFont font = workbook.createFont();

		font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		style.setFont(font);
		style.setFillBackgroundColor(HSSFColor.WHITE.index);
		return style;
	}
	

	
	/**
	 * 将EXCEL中A,B,C,D,E列映射成0,1,2,3
	 * 
	 * @param col
	 */
	private static int getExcelCol(String col) {
		col = col.toUpperCase();
		// 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。
		int count = -1;
		char[] cs = col.toCharArray();
		for (int i = 0; i < cs.length; i++) {
			count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);
		}
		return count;
	}

	/**
	 * 设置单元格上提示
	 * 
	 * @param sheet
	 *            要设置的sheet.
	 * @param promptfieldName
	 *            标题
	 * @param promptContent
	 *            内容
	 * @param firstRow
	 *            开始行
	 * @param endRow
	 *            结束行
	 * @param firstCol
	 *            开始列
	 * @param endCol
	 *            结束列
	 * @return 设置好的sheet.
	 */
	private static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptfieldName,
			String promptContent, int firstRow, int endRow, int firstCol,
			int endCol) {
		// 构造constraint对象
		DVConstraint constraint = DVConstraint
				.createCustomFormulaConstraint("DD1");
		// 四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList regions = new CellRangeAddressList(firstRow,
				endRow, firstCol, endCol);
		// 数据有效性对象
		HSSFDataValidation data_validation_view = new HSSFDataValidation(
				regions, constraint);
		data_validation_view.createPromptBox(promptfieldName, promptContent);
		sheet.addValidationData(data_validation_view);
		return sheet;
	}

	/**
	 * 设置某些列的值只能输入预制的数据,显示下拉框.
	 * 
	 * @param sheet
	 *            要设置的sheet.
	 * @param textlist
	 *            下拉框显示的内容
	 * @param firstRow
	 *            开始行
	 * @param endRow
	 *            结束行
	 * @param firstCol
	 *            开始列
	 * @param endCol
	 *            结束列
	 * @return 设置好的sheet.
	 */
	private static HSSFSheet setHSSFValidation(HSSFSheet sheet,
			String[] textlist, int firstRow, int endRow, int firstCol,
			int endCol) {
		// 加载下拉列表内容
		DVConstraint constraint = DVConstraint
				.createExplicitListConstraint(textlist);
		// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList regions = new CellRangeAddressList(firstRow,
				endRow, firstCol, endCol);
		// 数据有效性对象
		HSSFDataValidation data_validation_list = new HSSFDataValidation(
				regions, constraint);
		sheet.addValidationData(data_validation_list);
		return sheet;
	}

	/**
	 * 得到实体类所有通过注解映射了数据表的字段
	 * 
	 * @param
	 * @return
	 */
	private List<Field> getMappedFiled(Class clazz, List<Field> fields) {
		if (fields == null) {
			fields = new ArrayList<Field>();
		}

		Field[] allFields = clazz.getDeclaredFields();// 得到所有定义字段
		// 得到所有field并存放到一个list中.
		for (Field field : allFields) {
			if (field.isAnnotationPresent(ExcelVOAttribute.class)) {
				fields.add(field);
			}
		}
		if (clazz.getSuperclass() != null
				&& !clazz.getSuperclass().equals(Object.class)) {
			getMappedFiled(clazz.getSuperclass(), fields);
		}

		return fields;
	}



	/**
	 * 导出excel成对象
	 * @param lists
	 * @param sheetNames
	 * @param output
	 * @return
	 * @throws IllegalAccessException 
	 * @throws IllegalArgumentException 
	 */
	private boolean exportExcel(List<T> lists[], String sheetNames[],
			OutputStream output) throws IllegalArgumentException, IllegalAccessException {
		if (lists.length != sheetNames.length) {
			System.out.println("数组长度不一致");
			return false;
		}

		HSSFWorkbook workbook = new HSSFWorkbook();// 产生工作薄对象
		int cnt = 0;
		for (int ii = 0; ii < lists.length; ii++) {
			List<T> list = lists[ii];
			String sheetName = sheetNames[ii];
			if(list == null || list.size() == 0 || StringUtil.isBlank(sheetName)){
				continue;
			}else{
				List<Field> fields = getMappedFiled(clazz, null);

				HSSFSheet sheet = workbook.createSheet();// 产生工作表对象

				workbook.setSheetName(cnt, sheetName);

				HSSFRow row;
				HSSFCell cell;// 产生单元格
				HSSFCellStyle style = workbook.createCellStyle();
				style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
				style.setFillBackgroundColor(HSSFColor.GREY_40_PERCENT.index);
				row = sheet.createRow(0);// 产生一行
				// 写入各个字段的列头名称
				for (int i = 0; i < fields.size(); i++) {
					Field field = fields.get(i);
					ExcelVOAttribute attr = field
							.getAnnotation(ExcelVOAttribute.class);
					int col = getExcelCol(attr.column());// 获得列号
					cell = row.createCell(col);// 创建列
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 设置列中写入内容为String类型
					cell.setCellValue(attr.name());// 写入列名

					// 如果设置了提示信息则鼠标放上去提示.
					if (!attr.prompt().trim().equals("")) {
						setHSSFPrompt(sheet, "", attr.prompt(), 1, 100, col, col);// 这里默认设了2-101列提示.
					}
					// 如果设置了combo属性则本列只能选择不能输入
					if (attr.combo().length > 0) {
						setHSSFValidation(sheet, attr.combo(), 1, 100, col, col);// 这里默认设了2-101列只能选择不能输入.
					}
					cell.setCellStyle(style);
				}

				int startNo = 0;
				int endNo = list.size();
				// 写入各条记录,每条记录对应excel表中的一行
				for (int i = startNo; i < endNo; i++) {
					row = sheet.createRow(i + 1 - startNo);
					T vo = (T) list.get(i); // 得到导出对象.
					for (int j = 0; j < fields.size(); j++) {
						Field field = fields.get(j);// 获得field.
						field.setAccessible(true);// 设置实体类私有属性可访问
						ExcelVOAttribute attr = field
								.getAnnotation(ExcelVOAttribute.class);
						// 根据ExcelVOAttribute中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
						if (attr.isExport()) {
							cell = row.createCell(getExcelCol(attr.column()));// 创建cell
							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							cell.setCellValue(field.get(vo) == null ? ""
									: String.valueOf(field.get(vo)));// 如果数据存在就填入,不存在填入空格.
						}
					}
				}
				cnt++;
			}
		}

		try {
			output.flush();
			workbook.write(output);
			output.close();
			return true;
		} catch (IOException e) {
			e.printStackTrace();
			System.out.println("Output is closed ");
			return false;
		}

	}

	/**
	 * 对list数据源将其里面的数据导入到excel表单
	 * 
	 * @param sheetName
	 *            工作表的名称
	 *            每个sheet中数据的行数,此数值必须小于65536
	 * @param output
	 *            java输出流
	 * @throws IllegalAccessException 
	 * @throws IllegalArgumentException 
	 */
	public boolean exportExcel(List<T> list, String sheetName,
			OutputStream output) throws IllegalArgumentException, IllegalAccessException {
		List<T>[] lists = new ArrayList[1];
		lists[0] = list;

		String[] sheetNames = new String[1];
		sheetNames[0] = sheetName;

		return exportExcel(lists, sheetNames, output);
	}
	
	/**
	 * 导入excel成map
	 * @param in
	 * @param fileName
	 * @param map
	 * @param loopAllSheet 是否遍历所有sheet的数据，true遍历所有；false只读取第一个sheet
	 * @return
	 * @throws Exception
	 */
	public static List<Map<String, Object>> getListByExcel(InputStream in,
			String fileName, Map<String, String> map,boolean loopAllSheet) throws Exception {
		// 创建Excel工作薄
		Workbook work = getWorkbook(in, fileName);
		if (null == work) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;

		List<Map<String, Object>> dataListMaps = new ArrayList<Map<String, Object>>();
		int readSheetNum = 1;
		// 遍历Excel中所有的sheet
		/*if(loopAllSheet){
			readSheetNum = work.getNumberOfSheets();
		}*/
		for (int i = 0; i < readSheetNum; i++) {
			sheet = work.getSheetAt(i);
			if (sheet == null) {
				continue;
			}
			List<String> fieldNameList = new ArrayList<String>();
			int colNum = 0; // 列数
			Row row0 = sheet.getRow(0);
			if (row0 == null) {
				throw new RuntimeException("导入模版不正确，请下载正确的模版导入");
			}
			List<String> modelKeyList = new ArrayList<String>(20);
			modelKeyList.addAll(map.keySet());

			colNum = row0.getPhysicalNumberOfCells();
			for (int i1 = 0; i1 < colNum; i1++) {
				String nameString = (String) getCellValue(row0
						.getCell((short) i1));
				if (StringUtils.isNotBlank(nameString)
						&& !nameString.equals(MapUtils.getString(map,
								modelKeyList.get(i1)))) {
					throw new RuntimeException("导入模版不正确，请下载正确的模版导入");
				}
				nameString = nameString.replace(" ", "");
				fieldNameList.add(nameString);// 得到列名
			}

			// 遍历当前sheet中的所有行
			for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
				row = sheet.getRow(j);
				if (row == null || row.getFirstCellNum() == j) {
					continue;
				}
				// 遍历所有的列
				Map<String, Object> mapCell = new HashMap<String, Object>();
				int cols = row.getPhysicalNumberOfCells();
				if(cols > fieldNameList.size()){
					cols = fieldNameList.size() - 1;
				}
				for (int y = row.getFirstCellNum(); y <= cols; y++) {
					cell = row.getCell(y);
					if (null != cell) {
						mapCell.put(fieldNameList.get(y), getCellValue(cell));
					}
				}
				Map<String, Object> mapNew = new HashMap<>();
				for (String key : map.keySet()) {
					mapNew.put(key, mapCell.get(map.get(key)));
				}
				dataListMaps.add(mapNew);
			}
		}
		work.close();
		return dataListMaps;
	}

	/**
	 * 描述：根据文件后缀，自适应上传文件的版本
	 * 
	 * @param inStr
	 *            ,fileName
	 * @return
	 * @throws Exception
	 */
	private static Workbook getWorkbook(InputStream inStr, String fileName)
			throws Exception {
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if (!inStr.markSupported()) {
			inStr = new PushbackInputStream(inStr, 8);
		}
		if (POIFSFileSystem.hasPOIFSHeader(inStr)) {
			return new HSSFWorkbook(inStr);
		}
		if (POIXMLDocument.hasOOXMLHeader(inStr)) {
			return new XSSFWorkbook(OPCPackage.open(inStr));
		}
		throw new Exception("解析的文件格式有误！");

	}

	/**
	 * 描述：对表格中数值进行格式化
	 * 
	 * @param cell
	 * @return
	 */
	private static Object getCellValue(Cell cell) {
		Object value = null;
		DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
		DecimalFormat df2 = new DecimalFormat("0.00"); // 格式化数字

		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			value = cell.getRichStringCellValue().getString();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if ("General".equals(cell.getCellStyle().getDataFormatString())) {
				value = df.format(cell.getNumericCellValue());
			} else if ("m/d/yy".equals(cell.getCellStyle()
					.getDataFormatString())) {
				value = sdf.format(cell.getDateCellValue());
			} else {
				value = df2.format(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = cell.getBooleanCellValue();
			break;
		case Cell.CELL_TYPE_BLANK:
			value = "";
			break;
		case Cell.CELL_TYPE_FORMULA:
			String cellValue = cell.getStringCellValue();
			if(StringUtil.isNotBlank(cellValue)){
				cellValue = cellValue.replace("\"","").trim();
			}
			value = cellValue;
			break;
		default:
			break;
		}
		return value;
	}
	
	/**
     * 
     * @param excelName  导出的EXCEL名字
     * @param sheetNames  导出的SHEET名字
     * @param headers      导出的表格的表头
     * @param ds_titles      导出的数据 map.get(key) 对应的 key
     * @param datas        数据集  List<Map>
     * @param response
     * @throws IOException
     */ 
    public static void exportMap(String excelName, String[] sheetNames,List<String[]> headers,List<String[]> ds_titles, List<List<Map<String,Object>>> datas ,int sheetCnt,HttpServletRequest request, HttpServletResponse response) throws IOException { 
           //设置文件名 
            String fileName = ""; 
            if(StringUtils.isNotEmpty(excelName)){ 
                fileName = excelName; 
            } 
            //创建一个工作薄 
            HSSFWorkbook wb = new HSSFWorkbook(); 
            for(int s = 0; s < sheetCnt; s++){
            	//创建一个sheet 
                HSSFSheet sheet = wb.createSheet(StringUtils.isNotEmpty(sheetNames[s]) ? sheetNames[s] : "sheet"+s);
            	wb.setSheetName(s,sheetNames[s]);
        		sheet.setDefaultRowHeightInPoints(25);
        		sheet.setDefaultColumnWidth(12);
        		//创建表头，如果没有跳过 
                int headerrow = 0; 
                if(headers != null && headers.get(s) != null){
                    HSSFRow  row = sheet.createRow(headerrow); 
                    //表头样式 
                    HSSFCellStyle style = wb.createCellStyle();   
                    HSSFFont font = wb.createFont(); 
                    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
                    font.setFontHeightInPoints((short) 11); 
                    style.setFont(font); 
                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);   
                    style.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
                    style.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
                    style.setBorderRight(HSSFCellStyle.BORDER_THIN); 
                    style.setBorderTop(HSSFCellStyle.BORDER_THIN); 
                    for (int i = 0; i < headers.get(s).length; i++) {   
                        HSSFCell cell = row.createCell(i);   
                        cell.setCellValue(headers.get(s)[i]);   
                        cell.setCellStyle(style);   
                    }   
                    headerrow++; 
                }
                
                //表格主体  解析list 
                if(datas != null && datas.get(s) != null){ 
                    List<HSSFCellStyle> styleList = new ArrayList<HSSFCellStyle>(); 
                    for (int i = 0; i < ds_titles.get(s).length; i++) {  //列数 
                        HSSFCellStyle style = wb.createCellStyle();   
                        HSSFFont font = wb.createFont();
                        font.setFontHeightInPoints((short) 10); 
                        style.setFont(font); 
                        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
                        style.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
                        style.setBorderRight(HSSFCellStyle.BORDER_THIN); 
                        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
                        styleList.add(style); 
                    } 
                    for (int i = 0; i < datas.get(s).size() ; i++) {  //行数 
                        HSSFRow  row = sheet.createRow(headerrow); 
                        Map<String, Object> map = datas.get(s).get(i); 
                        for (int j = 0; j <ds_titles.get(s).length; j++) {  //列数 
                             HSSFCell cell = row.createCell(j);   
                             Object o = map.get(ds_titles.get(s)[j]); 
                             if(o==null||"".equals(o)){ 
                                 cell.setCellValue(""); 
                             }else { 
                                 cell.setCellValue(map.get(ds_titles.get(s)[j])+"");  
                             } 
                             cell.setCellStyle((HSSFCellStyle)styleList.get(j));   
                        } 
                        headerrow++; 
                    } 
                }
            }
             
             
              
            fileName=fileName+".xls"; 
            String filename = ""; 
            try{ 
               filename =encodeChineseDownloadFileName(request,fileName); 
            }catch(Exception e){ 
                e.printStackTrace(); 
            } 
//          final String userAgent = request.getHeader("USER-AGENT"); 
//            if(userAgent.indexOf( "MSIE")!=-1){//IE浏览器 
//              filename = URLEncoder.encode(fileName,"UTF8"); 
//            }else if(userAgent.indexOf( "Mozilla")!=-1){//google,火狐浏览器 
//              filename = new String(fileName.getBytes(), "ISO8859-1"); 
//            }else{ 
//              filename = URLEncoder.encode(fileName,"UTF8");//其他浏览器 
//            } 
               
            response.setHeader("Content-disposition", filename); 
            response.setContentType("application/vnd.ms-excel");   
            response.setHeader("Content-disposition", "attachment;filename="+filename);   
            response.setHeader("Pragma", "No-cache"); 
            OutputStream ouputStream = response.getOutputStream();   
            wb.write(ouputStream);   
            ouputStream.flush();   
            ouputStream.close(); 
               
    } 
    
    /** 
     * 对文件流输出下载的中文文件名进行编码 屏蔽各种浏览器版本的差异性 
     * @throws UnsupportedEncodingException  
     */   
    public static String encodeChineseDownloadFileName(   
            HttpServletRequest request, String pFileName) throws Exception {   
             
         String filename = null;     
            String agent = request.getHeader("USER-AGENT");     
            if (null != agent){     
                if (-1 != agent.indexOf("Firefox")) {//Firefox     
                    filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8"))))+ "?=";     
                }else if (-1 != agent.indexOf("Chrome")) {//Chrome     
                    filename = new String(pFileName.getBytes(), "ISO8859-1");     
                } else {//IE7+     
                    filename = java.net.URLEncoder.encode(pFileName, "UTF-8");     
                    filename = filename.replace("+", "%20"); 
                }     
            } else {     
                filename = pFileName;     
            }     
            return filename;    
    }   
	

	/**
	 *
	 * @param workbook
	 * @param worksheet
	 * @param sourceRowNum 被复制的行
	 * @param destinationRowNum 从此行开始插入
	 * @param rows 共同插入的行数
	 */
	public static void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum, int rows){
		HSSFRow newRow = worksheet.getRow(destinationRowNum);
		HSSFRow sourceRow = worksheet.getRow(sourceRowNum);
		if (newRow != null)
		{
			worksheet.shiftRows(destinationRowNum,worksheet.getLastRowNum(),rows,true,false);
		}else
		{
			newRow = worksheet.createRow(destinationRowNum);
		}
		for (int j = 0; j < rows; j++) {
			newRow = worksheet.createRow(destinationRowNum+j);
			for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
				HSSFCell oldCell = sourceRow.getCell(i);
				HSSFCell newCell = newRow.createCell(i);
				if(oldCell == null)
				{
					newCell = null;
					continue;
				}

				HSSFCellStyle newCellStyle = workbook.createCellStyle();
				newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
				newCell.setCellStyle(newCellStyle);
				if(oldCell.getCellComment() != null)
				{
					newCell.setCellComment(oldCell.getCellComment());
				}
				if(oldCell.getHyperlink() != null)
				{
					newCell.setHyperlink(oldCell.getHyperlink());
				}

				newCell.setCellType(oldCell.getCellType());

				switch (oldCell.getCellType())
				{
					case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue());
						break;
					case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue());
						break;
					case Cell.CELL_TYPE_ERROR: newCell.setCellValue(oldCell.getErrorCellValue());
						break;
					case Cell.CELL_TYPE_FORMULA: newCell.setCellValue(oldCell.getCellFormula());
						break;
					case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue());
						break;
					case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue());
						break;
				}
			}
		}

	}

	/**
	 * 导入车站、明挖区间主体施工形象进度
	 * @param fileName 包含后缀名
	 * @param is 导入的文件内容流
	 * @return
	 */
	public  static List<Map<String, Object>> readFromSiteMainWorkScheduleExcel(String fileName, InputStream is,ArrayList<SysDict> siteProjectNameList) throws ParseException {
		String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
		fileName = fileName.substring(0,fileName.lastIndexOf("."));
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>() ;
		boolean isHaveSite = false;
		try {

			Workbook wb = null;
			if (suffix.equalsIgnoreCase("xlsx"))
			{
				wb = WorkbookFactory.create(is);
				is.close();
			}
			if (suffix.equalsIgnoreCase("xls"))
			{
				POIFSFileSystem poifsFileSystem = new POIFSFileSystem(is);
				wb = new HSSFWorkbook(poifsFileSystem);
				is.close();
			}
			Sheet sheet = null;
			int sheetNumber = wb.getNumberOfSheets();
			for (int i = 0; i < sheetNumber; i++) {
				String sheetName = wb.getSheetName(i);
				if (sheetName.contains("主体施工进度"))
					sheet = wb.getSheetAt(i);
			}
			//工作表对象
			//Sheet sheet = wb.getSheetAt(0);
			if (sheet == null)
				return null;
			//总行数
			int rowLength = sheet.getLastRowNum()+1;
			//工作表的列
			Row row = sheet.getRow(0);
			System.out.println("行数：" + rowLength);
			ArrayList<String> siteNameArray = new ArrayList<String>();
            ArrayList<Integer> dataTitleIndexArray = new ArrayList<Integer>();
			ArrayList<String> bidNameArray = new ArrayList<String>();
			ArrayList<String> ceilingTimeArray = new ArrayList<String>();
			int beginDataColIndex = 0;//数据开始列索引
			int beginDataRowIndex = 0;
			String projectName = "";
			String subProjectName = "";
			int curRowIndex = 0;
			for (int i = 0; i < rowLength; i++) {
				curRowIndex = i;
				Map<String, Object> rowMap = new HashMap<String, Object>();
				row = sheet.getRow(i);
				int colLength = row.getLastCellNum();
				String curSiteName = "";
				int  curDataTitleIndex = -1;
				String curBidName = "";
				String ceilingTime = "";
				String current = null;
				String cumulative = null;
				String total = null;
				String completeRate = null;
				String isSum = null;
				if (isHaveSite == false)
				{
					for (int j = 0; j < colLength; j++) {
						Cell cell = row.getCell(j);
						String c = getCellValueEx(cell);
						if (c==null)
						    break;
						if (c.trim().equalsIgnoreCase("合计"))
						{
							if (beginDataColIndex == 0)
							{
								beginDataColIndex = j;
								beginDataRowIndex = i+1;
							}

							siteNameArray.add(c);
							dataTitleIndexArray.add(j);
							ceilingTimeArray.add("");
							bidNameArray.add("");
							break;
						}
						if (c != null && c.equalsIgnoreCase("站点"))//判断此行是不是站点标题
						{
							Cell cell1 = row.getCell(0);//获取序号值
							String c1 = getCellValueEx(cell1);
							/* 没有序号，以前判断为格式不合格，现在可以忽略
							if (!c1.equalsIgnoreCase("序号"))//不是序号，返回
								return null;*/
							isHaveSite = true;
							continue;
						}
						if (isHaveSite && c!= null && c.length() > 0 && curRowIndex == i)
						{
							if (beginDataColIndex == 0)
							{
								beginDataColIndex = j;
								beginDataRowIndex = i+1;
							}

							if(c.indexOf("站（") ==-1 || c.indexOf("标）") ==-1)
								continue;//return  null; // 之前没有站或标名称，判断格式不合格，现在跳过
							String  ceilingTimeStr = "";
							int index = c.indexOf("站");
							String siteName = c.substring(0,index+1);

							String bidName = c.substring(c.indexOf("（")+1,c.indexOf("）"));

							int endIndex = c.indexOf("封顶");
							if ( endIndex > 0)
							{
								index = c.indexOf("【");
								ceilingTimeStr = c.substring(index+1,endIndex-1);
								ceilingTimeStr = "20" + ceilingTimeStr;
								ceilingTimeStr = ceilingTimeStr.replace("年","-").replace("月","-");
							}
							siteNameArray.add(siteName.trim());
							dataTitleIndexArray.add(j);
							bidNameArray.add(bidName.trim());
							ceilingTimeArray.add(ceilingTimeStr);

						}

					}
				}else
				{
					for (int j = 0; j < colLength; j++) {
						Cell cell = row.getCell(j);
						String c = getCellValueEx(cell);

						if(c == null)
						{
							break;

						}

						if (j==0 && c != null && c.length()==0)//如果此行为空，则跳过
						{
							break;
						}
						if (c != null && c.equalsIgnoreCase("站点"))//
						{
							isHaveSite = false;
							--i;

							//重新初始化
							siteNameArray = new ArrayList<String>();
							dataTitleIndexArray = new ArrayList<Integer>();
							bidNameArray = new ArrayList<String>();
							ceilingTimeArray = new ArrayList<String>();
							beginDataColIndex = 0;//数据开始列索引
							projectName = "";
							subProjectName = "";
							curRowIndex = 0;

							break;//此行重新处理
						}
						if (c != null && c.equalsIgnoreCase("项目"))
						{
							break;
						}
						if (i < beginDataRowIndex)//当前行小于数据开始行，则跳过
						{
							break;
						}
						if (j == 1 && c.length() >0)//第1列，并且不是空值，保存主项目名称
						{
							projectName = c;
						}
						if (j == 2 && c.length() >0)//第2列，并且不是空值，保存子项目名称
						{
							subProjectName = c;
							for (int k = 0; k < siteProjectNameList.size(); k++) {//找出当前子项目对应的code
								String tempName = siteProjectNameList.get(k).getDictValue();
								if (c.indexOf(tempName) > -1)
								{
									subProjectName = siteProjectNameList.get(k).getCode();
									break;
								}
							}
						}else if (j == 2 && c.length() ==0)//此项目为空则换行
						{
							break;
						}
						if (j == beginDataColIndex && j > 0 )//开始有数据的列，本期
						{

							for (int k = 0; k < siteNameArray.size(); k++) {
								rowMap = new HashMap<String, Object>();
								rowMap.put("projectName",projectName);
								rowMap.put("subProjectName",subProjectName);

								curSiteName = siteNameArray.get(k);
								curDataTitleIndex = dataTitleIndexArray.get(k);
								curBidName = bidNameArray.get(k);
								ceilingTime = ceilingTimeArray.get(k);
								int curDataColBeginIndex = curDataTitleIndex;//beginDataColIndex + k*3;
								Cell tempCell = row.getCell(curDataColBeginIndex);
								current = getCellValueEx(tempCell);

								tempCell = row.getCell(curDataColBeginIndex+1);
								cumulative = getCellValueEx(tempCell);
								tempCell = row.getCell(curDataColBeginIndex+2);
								total = getCellValueEx(tempCell);

								if (curSiteName.equalsIgnoreCase("合计"))//最后合计特殊取值
								{
									tempCell = row.getCell(curDataColBeginIndex+3);
									completeRate = getCellValueEx(tempCell)==null?"0":getCellValueEx(tempCell).replace("%","");
									isSum = "1";
								}else
								{
									isSum = "0";
								}
								rowMap.put("siteName",curSiteName);
								rowMap.put("bidName",curBidName);
								rowMap.put("ceilingTime",ceilingTime);
								rowMap.put("current",current);
								rowMap.put("cumulative",cumulative);
								rowMap.put("total",total);
								rowMap.put("completeRate",completeRate);
								rowMap.put("isSum",isSum);
								rowMap.put("source",0);
								resultList.add(rowMap);
							}
							break;
						}

					}
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return null;
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		} catch (InvalidFormatException e) {
			e.printStackTrace();
			return null;
		}
		return resultList;
	}

	/**
	 * 导入出入段线、明挖区间及控制中心主体施工形象进度，和车站格式相同，就是站点名称改成了出入段线
	 * @param fileName 包含后缀名
	 * @param is 导入的文件内容流
	 * @return
	 */
	public  static List<Map<String, Object>> readFromSectionMainWorkScheduleExcel(String fileName, InputStream is,ArrayList<SysDict> siteProjectNameList) throws ParseException {
		String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
		fileName = fileName.substring(0,fileName.lastIndexOf("."));
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>() ;
		boolean isHaveSection = false;
		try {

			Workbook wb = null;
			if (suffix.equalsIgnoreCase("xlsx"))
			{
				wb = WorkbookFactory.create(is);
				is.close();
			}
			if (suffix.equalsIgnoreCase("xls"))
			{
				POIFSFileSystem poifsFileSystem = new POIFSFileSystem(is);
				wb = new HSSFWorkbook(poifsFileSystem);
				is.close();
			}
			//工作表对象
			Sheet sheet = wb.getSheetAt(0);
			//总行数
			int rowLength = sheet.getLastRowNum()+1;
			//工作表的列
			Row row = sheet.getRow(0);
			System.out.println("行数：" + rowLength);
			ArrayList<String> sectionNameArray = new ArrayList<String>();
            ArrayList<Integer> dataTitleIndexArray = new ArrayList<Integer>();
			ArrayList<String> bidNameArray = new ArrayList<String>();
			int beginDataColIndex = 0;//数据开始列索引
			int beginDataRowIndex = 0;
			String projectName = "";
			String subProjectName = "";
			int curRowIndex = 0;
			for (int i = 0; i < rowLength; i++) {
				curRowIndex = i;
				Map<String, Object> rowMap = new HashMap<String, Object>();
				row = sheet.getRow(i);
				int colLength = row.getLastCellNum();
				String curSectionName = "";
				int curDataTitleIndex = -1;
				String curBidName = "";
				String current = null;
				String cumulative = null;
				String total = null;
				String completeRate = null;
				String isSum = null;
				if (isHaveSection == false)
				{
					for (int j = 0; j < colLength; j++) {
						Cell cell = row.getCell(j);
						String c = getCellValueEx(cell);
                        if (c==null)
                            break;
						if (c.trim().equalsIgnoreCase("合计"))
						{
							if (beginDataColIndex == 0)
							{
								beginDataColIndex = j;
								beginDataRowIndex = i+1;
							}

							sectionNameArray.add(c);
							dataTitleIndexArray.add(j);
							bidNameArray.add("");
							break;
						}
						if (c != null && c.equalsIgnoreCase("出入场线及区间"))//判断此行是不是区间标题
						{
							isHaveSection = true;
							continue;
						}
						if (isHaveSection && c!= null && c.length() > 0 && curRowIndex == i)
						{
							if (beginDataColIndex == 0)
							{
								beginDataColIndex = j;
								beginDataRowIndex = i+1;
							}

							int endIndex = c.indexOf("标）");
							if( endIndex ==-1)
                                continue;//return  null; // 之前没有标名称，判断格式不合格，现在跳过
							int beginIndex = c.lastIndexOf("（");
							if( beginIndex ==-1)
                                continue;//return  null; // 之前没有标名称，判断格式不合格，现在跳过
							String bidName = c.substring(beginIndex+1,c.indexOf("标）")+1);

							String sectionName = c.substring(0,beginIndex);
							if (sectionName.trim().length()==0)
                                continue;//return  null; // 之前没有标名称，判断格式不合格，现在跳过
							sectionNameArray.add(sectionName.trim());
							dataTitleIndexArray.add(j);
							bidNameArray.add(bidName.trim());

						}

					}
				}else
				{
					for (int j = 0; j < colLength; j++) {
						Cell cell = row.getCell(j);
						String c = getCellValueEx(cell);

						if(c == null)
						{
							break;

						}

						if (j==0 && c != null && c.length()==0)//如果此行为空，则跳过
						{
							break;
						}
						if (c != null && c.equalsIgnoreCase("出入场线及区间"))//
						{
							isHaveSection = false;
							--i;

							//重新初始化
							sectionNameArray = new ArrayList<String>();
							dataTitleIndexArray = new ArrayList<Integer>();
							bidNameArray = new ArrayList<String>();
							beginDataColIndex = 0;//数据开始列索引
							projectName = "";
							subProjectName = "";
							curRowIndex = 0;

							break;//此行重新处理
						}
						if (c != null && c.equalsIgnoreCase("项目"))
						{
							break;
						}
						if (i < beginDataRowIndex)//当前行小于数据开始行，则跳过
						{
							break;
						}
						if (j == 1 && c.length() >0)//第1列，并且不是空值，保存主项目名称
						{
							projectName = c;
						}
						if (j == 2 && c.length() >0)//第2列，并且不是空值，保存子项目名称
						{
							subProjectName = c;
							for (int k = 0; k < siteProjectNameList.size(); k++) {//找出当前子项目对应的code
								String tempName = siteProjectNameList.get(k).getDictValue();
								if (c.indexOf(tempName) > -1)
								{
									subProjectName = siteProjectNameList.get(k).getCode();
									break;
								}
							}
						}else if (j == 2 && c.length() ==0)//此项目为空则换行
						{
							break;
						}
						if (j == beginDataColIndex  && j > 0 )//开始有数据的列，本期
						{

							for (int k = 0; k < sectionNameArray.size(); k++) {
								rowMap = new HashMap<String, Object>();
								rowMap.put("projectName",projectName);
								rowMap.put("subProjectName",subProjectName);

								curSectionName = sectionNameArray.get(k);
								curDataTitleIndex = dataTitleIndexArray.get(k);
								curBidName = bidNameArray.get(k);
								int curDataColBeginIndex = curDataTitleIndex;//beginDataColIndex + k*3;
								Cell tempCell = row.getCell(curDataColBeginIndex);
								current = getCellValueEx(tempCell);
								tempCell = row.getCell(curDataColBeginIndex+1);
								cumulative = getCellValueEx(tempCell);
								tempCell = row.getCell(curDataColBeginIndex+2);
								total = getCellValueEx(tempCell);

								if (curSectionName.equalsIgnoreCase("合计"))//最后合计特殊取值
								{
//									tetmpCell = row.getCell(curDataColBeginIndex+3);
//									completeRate = getCellValueEx(tetmpCell)==null?"0":getCellValueEx(tempCell).replace("%","");
									isSum = "1";
								}else
								{
									isSum = "0";
								}
								rowMap.put("siteName",curSectionName);
								rowMap.put("bidName",curBidName);
								rowMap.put("current",current);
								rowMap.put("cumulative",cumulative);
								rowMap.put("total",total);
								rowMap.put("isSum",isSum);
								rowMap.put("source",1);
								resultList.add(rowMap);
							}
							break;
						}

					}
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return null;
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		} catch (InvalidFormatException e) {
			e.printStackTrace();
			return null;
		}
		return resultList;
	}

	/**
	 * 导入盾构管片生产进度
	 * @param fileName 包含后缀名
	 * @param is 导入的文件内容流
	 * @return
	 */
	public  static List<Map<String, Object>> readFromSegmentMainWorkScheduleExcel(String fileName, InputStream is) throws ParseException {
		String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
		fileName = fileName.substring(0,fileName.lastIndexOf("."));
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>() ;
		boolean isHaveSegment = false;
		try {

			Workbook wb = null;
			if (suffix.equalsIgnoreCase("xlsx"))
			{
				wb = WorkbookFactory.create(is);
				is.close();
			}
			if (suffix.equalsIgnoreCase("xls"))
			{
				POIFSFileSystem poifsFileSystem = new POIFSFileSystem(is);
				wb = new HSSFWorkbook(poifsFileSystem);
				is.close();
			}
			//工作表对象
			Sheet sheet = wb.getSheetAt(0);
			//总行数
			int rowLength = sheet.getLastRowNum()+1;
			//工作表的列
			Row row = sheet.getRow(0);
			System.out.println("行数：" + rowLength);
			ArrayList<String> dataTitleNameArray = new ArrayList<String>();//数据大标题名称，比如钢模数量（套）、砼管片（环）、钢管片（吨）
			ArrayList<Integer> dataTitleIndexArray = new ArrayList<Integer>();
			int beginDataColIndex = 0;//数据开始列索引
			int beginDataRowIndex = 0;//数据开始行索引
			String manufacturer = "";
			int curRowIndex = 0;
			for (int i = 0; i < rowLength; i++) {
				curRowIndex = i;
				Map<String, Object> rowMap = new HashMap<String, Object>();
				row = sheet.getRow(i);
				int colLength = row.getLastCellNum();
				String curDataTitleName = "";
				int curDataTitleIndex = -1;
                String segmentKind = "";
				String steelMouldAmount = null;
				String contractAmount = null;
				String currentProduce = null;
				String cumulativeProduce = null;
				String currentDespatch = null;
				String cumulativeDespatch = null;
				String stock = null;
				String completeRate = null;
				String isSum = null;
				if (isHaveSegment == false)
				{
					for (int j = 0; j < colLength; j++) {
						Cell cell = row.getCell(j);
						String c = getCellValueEx(cell);

						if (c != null && c.equalsIgnoreCase("管片厂商"))//判断此行是不是标题行
						{
							isHaveSegment = true;
							continue;
						}
						if (isHaveSegment && c!= null && c.length() > 0 && curRowIndex == i)
						{
							if (beginDataColIndex == 0)
							{
								beginDataColIndex = j;
								beginDataRowIndex = i+1;
							}
							if( c.indexOf("钢模数量") ==-1 && c.indexOf("砼管片")==-1 && c.indexOf("钢管片")==-1)//一个都没有，说明格式不对
								return  null;
							dataTitleNameArray.add(c);
							dataTitleIndexArray.add(j);
						}
					}
				}else
				{
					if (dataTitleNameArray.size() != 3)//不等于3，说明格式不对。包含：钢模数量（套）、砼管片（环）、钢管片（吨）
					{
						return null;
					}
					for (int j = 0; j < colLength; j++) {
						Cell cell = row.getCell(j);
						String c = getCellValueEx(cell);
						if(c == null)
						{
							break;
						}
						if (j==0 && c != null && c.length()==0)//如果此行为空，则跳过
						{
							break;
						}
						if (c != null && c.equalsIgnoreCase("管片厂商"))//
						{
							isHaveSegment = false;
							--i;

							//重新初始化
							dataTitleNameArray = new ArrayList<String>();
							dataTitleIndexArray = new ArrayList<Integer>();
							beginDataColIndex = 0;//数据开始列索引
							manufacturer = "";
							curRowIndex = 0;
							break;//此行重新处理
						}
						if (i < beginDataRowIndex)//当前行小于数据开始行，则跳过
						{
							break;
						}
						if (j == 1 && c.length() >0)//第1列，并且不是空值，保存主项目名称
						{
							manufacturer = c;
						}
						if (j == beginDataColIndex  && j > 0 )//开始有数据的列，本期
						{
							for (int k = 0; k < dataTitleNameArray.size(); k++) {
								rowMap = new HashMap<String, Object>();
								rowMap.put("manufacturer",manufacturer);
								curDataTitleName = dataTitleNameArray.get(k);
								curDataTitleIndex = dataTitleIndexArray.get(k);
								if (curDataTitleName.indexOf("钢模数量") > -1)
								{
									Cell tetmpCell = row.getCell(curDataTitleIndex);
									steelMouldAmount = getCellValueEx(tetmpCell);
                                   segmentKind = "";
									continue;
								}else if (curDataTitleName.indexOf("砼管片") > -1)
								{
									Cell tetmpCell = row.getCell(curDataTitleIndex);
									contractAmount = getCellValueEx(tetmpCell);//合同量

									tetmpCell = row.getCell(curDataTitleIndex+1);
									currentProduce = getCellValueEx(tetmpCell);//本周生产

									tetmpCell = row.getCell(curDataTitleIndex+2);//累计生产
									cumulativeProduce = getCellValueEx(tetmpCell);

									tetmpCell = row.getCell(curDataTitleIndex+3);
									currentDespatch = getCellValueEx(tetmpCell);

									tetmpCell = row.getCell(curDataTitleIndex+4);
									cumulativeDespatch = getCellValueEx(tetmpCell);

									tetmpCell = row.getCell(curDataTitleIndex+5);
									stock = getCellValueEx(tetmpCell);

									tetmpCell = row.getCell(curDataTitleIndex+6);
									completeRate = getCellValueEx(tetmpCell);

                                    segmentKind = "砼管片（环）";
								}else if (curDataTitleName.indexOf("钢管片") > -1)
								{
									Cell tetmpCell = row.getCell(curDataTitleIndex);
									contractAmount = getCellValueEx(tetmpCell);//合同量

									tetmpCell = row.getCell(curDataTitleIndex+1);
									currentProduce = getCellValueEx(tetmpCell);//本周生产

									tetmpCell = row.getCell(curDataTitleIndex+2);//累计生产
									cumulativeProduce = getCellValueEx(tetmpCell);

									tetmpCell = row.getCell(curDataTitleIndex+3);
									stock = getCellValueEx(tetmpCell);

									tetmpCell = row.getCell(curDataTitleIndex+4);
									completeRate = getCellValueEx(tetmpCell);

                                    segmentKind = "钢管片（吨）";
								}

								if (manufacturer.equalsIgnoreCase("合计"))//最后合计特殊取值
								{
									isSum = "1";
								}else
								{
									isSum = "0";
								}
								rowMap.put("manufacturer",manufacturer);
								rowMap.put("segmentKind",segmentKind);
								rowMap.put("steelMouldAmount",steelMouldAmount);
								rowMap.put("contractAmount",contractAmount);
								rowMap.put("currentProduce",currentProduce);
								rowMap.put("cumulativeProduce",cumulativeProduce);
								rowMap.put("currentDespatch",currentDespatch);
								rowMap.put("cumulativeDespatch",cumulativeDespatch);
								rowMap.put("stock",stock);
								rowMap.put("completeRate",completeRate);
								rowMap.put("isSum",isSum);
								resultList.add(rowMap);
							}
							break;
						}
					}
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return null;
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		} catch (InvalidFormatException e) {
			e.printStackTrace();
			return null;
		}
		return resultList;
	}

    /**
     * 导入盾构掘进施工形象进度
     * @param fileName 包含后缀名
     * @param is 导入的文件内容流
     * @return
     */
    public  static List<Map<String, Object>> readFromTunnellingWorkScheduleExcel(String fileName, InputStream is) throws ParseException {
        String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
        fileName = fileName.substring(0,fileName.lastIndexOf("."));
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>() ;
        boolean isHaveTunnelling = false;
        try {

            Workbook wb = null;
            if (suffix.equalsIgnoreCase("xlsx"))
            {
                wb = WorkbookFactory.create(is);
                is.close();
            }
            if (suffix.equalsIgnoreCase("xls"))
            {
                POIFSFileSystem poifsFileSystem = new POIFSFileSystem(is);
                wb = new HSSFWorkbook(poifsFileSystem);
                is.close();
            }
            //工作表对象
            Sheet sheet = wb.getSheetAt(0);
            //总行数
            int rowLength = sheet.getLastRowNum()+1;
            //工作表的列
            Row row = sheet.getRow(0);
            System.out.println("行数：" + rowLength);
            ArrayList<String> dataTitleNameArray = new ArrayList<String>();//数据大标题名称，比如钢模数量（套）、砼管片（环）、钢管片（吨）
            ArrayList<Integer> dataTitleIndexArray = new ArrayList<Integer>();
            int beginDataColIndex = 0;//数据开始列索引
            int beginDataRowIndex = 0;//数据开始行索引
            String bidName = "";
            int curRowIndex = 0;
            for (int i = 0; i < rowLength; i++) {
                curRowIndex = i;
                Map<String, Object> rowMap = new HashMap<String, Object>();
                row = sheet.getRow(i);
                int colLength = row.getLastCellNum();
                String curDataTitleName = "";
                int curDataTitleIndex = -1;
                String beginSiteName = null;
                String endSiteName = null;
                String intervalName = null;
                String direction = null;
                String endShaftReinforce = null;
                String startTime = null;
                String intervalLength = null;
                String currentComplete = null;
                String cumulativeComplete = null;
                String completeRate = null;
                String remark = null;
                String isSum = null;
                if (isHaveTunnelling == false)
                {
                    for (int j = 0; j < colLength; j++) {
                        Cell cell = row.getCell(j);
                        String c = getCellValueEx(cell);

                        if (c != null && c.equalsIgnoreCase("区间名称"))//判断此行是不是标题行
                        {
                            isHaveTunnelling = true;
                            continue;
                        }
                        if (isHaveTunnelling && c!= null && c.length() > 0 && curRowIndex == i)
                        {
                            if (beginDataColIndex == 0)
                            {
                                beginDataColIndex = j;
                                beginDataRowIndex = i+1;
                            }
                            if( c.indexOf("端头井加固") ==-1 && c.indexOf("始发时间")==-1 && (c.indexOf("区间长度")==-1 && (c.indexOf("区间")==-1 && c.indexOf("长度")==-1))
                                    && c.indexOf("本周完成")==-1 && c.indexOf("累计完成")==-1 && c.indexOf("隧道施工进度")==-1 && c.indexOf("备注")==-1)//一个都没有，说明格式不对
                                return  null;
                            if (c.indexOf("隧道施工进度") >-1)//忽略此列
                                continue;
                            dataTitleNameArray.add(c);
                            dataTitleIndexArray.add(j);
                        }
                    }
                }else
                {
                    /*if (dataTitleNameArray.size() != 3)//不等于3，说明格式不对。包含：钢模数量（套）、砼管片（环）、钢管片（吨）
                    {
                        return null;
                    }*/
                    rowMap = new HashMap<String, Object>();

                    for (int j = 0; j < colLength; j++) {
                        Cell cell = row.getCell(j);
                        String c = getCellValueEx(cell);

						if(c == null)
						{
							break;
						}

                        if (j==0 && c != null && c.length()==0)//
                        {
							Cell tetmpCell = row.getCell(1);
							String   tetmpC= getCellValueEx(tetmpCell);
							if (tetmpC != null && tetmpC.length()==0)//此行前两列都为空，则跳过
								break;
                        }
                        if (c != null && c.equalsIgnoreCase("区间名称"))//
                        {
                            isHaveTunnelling = false;
                            --i;

                            //重新初始化
                            dataTitleNameArray = new ArrayList<String>();
                            dataTitleIndexArray = new ArrayList<Integer>();
                            beginDataColIndex = 0;//数据开始列索引
                            bidName = "";
                            curRowIndex = 0;
                            break;//此行重新处理
                        }
                        if (i < beginDataRowIndex)//当前行小于数据开始行，则跳过
                        {
                            break;
                        }
                        if (j == 0 && c.length() >0)//第1列，并且不是空值，标段
                        {
                            bidName = c;
                            if (c.indexOf("合计") > -1)
                            {
								intervalName = "合计";
								//rowMap.put("intervalName","合计");
                                /*rowMap.put("intervalLength",intervalLength);
                                rowMap.put("currentComplete",currentComplete);
                                rowMap.put("cumulativeComplete",cumulativeComplete);
                                rowMap.put("remark",remark);*/
                                isSum = "1";
								 //resultList.add(rowMap);
                            }else
							{
								bidName = bidName.replaceFirst("^0*", "");
							}
                        }
                        if (j == 1 && c.length() >0)//第1列，并且不是空值，区间名称
                        {
                            intervalName = c;
                            if (intervalName.indexOf("～")==-1 || (intervalName.indexOf("（左线）") == -1 && intervalName.indexOf("（右线）")==-1) )
                            {
                                return  null;
                            }
                            beginSiteName = intervalName.substring(0,intervalName.indexOf("～"));
                            endSiteName = intervalName.substring(intervalName.indexOf("～")+1,intervalName.indexOf("（"));
                            direction = intervalName.substring(intervalName.indexOf("（")+1,intervalName.indexOf("）"));
							isSum = "0";
                        }

                        if (j == beginDataColIndex  && j > 0 )//开始有数据的列
                        {

                            rowMap.put("bidName",bidName);
                            rowMap.put("intervalName",intervalName);
							rowMap.put("beginSiteName",beginSiteName);
                            rowMap.put("endSiteName",endSiteName);
                            rowMap.put("direction",direction);

                            for (int k = 0; k < dataTitleNameArray.size(); k++) {
                                curDataTitleName = dataTitleNameArray.get(k);
                                curDataTitleIndex = dataTitleIndexArray.get(k);
                                if (curDataTitleName.indexOf("端头井加固") > -1 && intervalName.indexOf("合计") ==-1)
                                {
                                    Cell tetmpCell = row.getCell(curDataTitleIndex);
                                    endShaftReinforce = getCellValueEx(tetmpCell);
                                    rowMap.put("endShaftReinforce",endShaftReinforce);
                                }else if (curDataTitleName.indexOf("始发时间") > -1 && intervalName.indexOf("合计") ==-1)
                                {
                                    Cell tetmpCell = row.getCell(curDataTitleIndex);
                                    startTime = getCellValueEx(tetmpCell);
                                    rowMap.put("startTime",startTime);
                                }else if (curDataTitleName.indexOf("区间长度") > -1 || curDataTitleName.indexOf("长度") > -1 || curDataTitleName.indexOf("区间") > -1)
                                {
                                    Cell tetmpCell = row.getCell(curDataTitleIndex);
                                    intervalLength = getCellValueEx(tetmpCell);
                                    rowMap.put("intervalLength",intervalLength);
                                }else if (curDataTitleName.indexOf("本周完成") > -1)
                                {
                                    Cell tetmpCell = row.getCell(curDataTitleIndex);
                                    currentComplete = getCellValueEx(tetmpCell);
                                    rowMap.put("currentComplete",currentComplete);
                                }else if (curDataTitleName.indexOf("累计完成") > -1)
                                {
                                    Cell tetmpCell = row.getCell(curDataTitleIndex);
                                    cumulativeComplete = getCellValueEx(tetmpCell);
                                    rowMap.put("cumulativeComplete",cumulativeComplete);
                                }else if (curDataTitleName.indexOf("备注") > -1)
                                {
                                    Cell tetmpCell = row.getCell(curDataTitleIndex);
                                    remark = getCellValueEx(tetmpCell);
                                    rowMap.put("remark",remark);
                                }
                            }
                            rowMap.put("isSum",isSum);
							resultList.add(rowMap);
                            break;
                        }
                    }

                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            return null;
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        } catch (InvalidFormatException e) {
            e.printStackTrace();
            return null;
        }
        return resultList;
    }

    /**
     * 导入车站附属结构施工进度
     * @param fileName 包含后缀名
     * @param is 导入的文件内容流
     * @return
     */
    public  static List<Map<String, Object>> readFromAccessorStructureScheduleExcel(String fileName, InputStream is) throws ParseException {
        String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
        fileName = fileName.substring(0,fileName.lastIndexOf("."));
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>() ;
        boolean isHaveAccessor = false;
        try {

            Workbook wb = null;
            if (suffix.equalsIgnoreCase("xlsx"))
            {
                wb = WorkbookFactory.create(is);
                is.close();
            }
            if (suffix.equalsIgnoreCase("xls"))
            {
                POIFSFileSystem poifsFileSystem = new POIFSFileSystem(is);
                wb = new HSSFWorkbook(poifsFileSystem);
                is.close();
            }
            //工作表对象
            Sheet sheet = wb.getSheetAt(0);
            //总行数
            int rowLength = sheet.getLastRowNum()+1;
            //工作表的列
            Row row = sheet.getRow(0);
            System.out.println("行数：" + rowLength);
            ArrayList<String> structureNameArray = new ArrayList<String>();//结构名称数组
            ArrayList<Integer> structureIndexArray = new ArrayList<Integer>();//结构名称列索引
            ArrayList<String> unitArray = new ArrayList<String>();

            int beginDataColIndex = 0;//数据开始列索引
            int beginDataRowIndex = 0;//数据开始行索引
            int curRowIndex = 0;
            for (int i = 0; i < rowLength; i++) {
                curRowIndex = i;
                row = sheet.getRow(i);
                int colLength = row.getLastCellNum();
                String siteName = "";
                String curStructureName = "";
                String curUnit = "";
                int curStructureIndex = -1;
                String designAmount = null;
                String underWork = null;
                String wholeComplete = null;
                String cumulativeComplete = null;
                String remark = "";
                String isSum = null;
                if (isHaveAccessor == false)
                {
                    for (int j = 0; j < colLength; j++) {
                        Cell cell = row.getCell(j);
                        String c = getCellValueEx(cell);

                        if (c != null && c.equalsIgnoreCase("车站名称"))//判断此行是不是标题行
                        {
                            isHaveAccessor = true;
                            continue;
                        }
                        if (isHaveAccessor && c!= null && c.length() > 0 && curRowIndex == i)
                        {
                            if (beginDataColIndex == 0)
                            {
                                beginDataColIndex = j;
                                beginDataRowIndex = i+1;
                            }
                            if( c.indexOf("出入口") ==-1 && c.indexOf("风亭")==-1 && c.indexOf("轨顶风道")==-1  && c.indexOf("站台板")==-1
                                    && c.indexOf("站内楼梯")==-1  && c.indexOf("备注")==-1 )//说明格式不对
                                return  null;
                            structureNameArray.add(c);
                            structureIndexArray.add(j);
                            int beginIndex = c.indexOf("（");
                            int endIndex = c.indexOf("）");
                            if (beginIndex != -1 && endIndex > beginIndex)
                            {
                                unitArray.add(c.substring(beginIndex+1,endIndex));
                            }
                        }
                    }
                }else
                {
                    if (structureNameArray.size() != 6)//不等于6，说明格式不对。
                    {
                        return null;
                    }
                    for (int j = 0; j < colLength; j++) {
                        Cell cell = row.getCell(j);
                        String c = getCellValueEx(cell);
						if(c == null)
						{
							break;
						}

                        if (j==1 && c != null && c.length()==0)//如果此行为空，则跳过
                        {
                            Cell tetmpCell = row.getCell(0);
                            String   tetmpC= getCellValueEx(tetmpCell);
                            if (tetmpC != null && tetmpC.length()==0)//此行前两列都为空，则跳过
                                break;
                        }
                        if (c != null && c.equalsIgnoreCase("车站名称"))//
                        {
                            isHaveAccessor = false;
                            --i;//此行重新处理

                            //重新初始化
                            structureNameArray = new ArrayList<String>();
                            structureIndexArray = new ArrayList<Integer>();
                            unitArray = new ArrayList<String>();
                            beginDataColIndex = 0;//数据开始列索引
                            curRowIndex = 0;
                            break;
                        }
                        if (i < beginDataRowIndex)//当前行小于数据开始行，则跳过
                        {
                            break;
                        }
                        if (j == 0 && c.length() >0)//第1列，并且不是空值，判断是否是合计
                        {
                            siteName = c;
                            if (siteName.equalsIgnoreCase("合计"))//最后合计特殊取值
                            {
                                isSum = "1";
                            }else
                            {
                                isSum = "0";
                            }
                        }
                        if (j == 1 && c.length() >0)//第2列，并且不是空值，获取站点名称
                        {
                            if (siteName.equalsIgnoreCase("合计"))//最后合计特殊取值
                            {
                            }else
                            {
                                siteName = c;
                            }
                        }
                        if (j == beginDataColIndex  && j > 0 )//开始有数据的列，本期
                        {
                            for (int k = 0; k < structureNameArray.size() -1; k++) {
                                curStructureName = structureNameArray.get(k);
                                curStructureIndex = structureIndexArray.get(k);
                                curUnit = unitArray.get(k);
                                Map<String, Object>  rowMap = new HashMap<String, Object>();
                                rowMap.put("siteName",siteName);
                                rowMap.put("structureName",curStructureName);
                                rowMap.put("unit",curUnit);
                                rowMap.put("isSum",isSum);

                                Cell tetmpCell = row.getCell(structureIndexArray.get(structureIndexArray.size()-1));//最后一个是备注
                                remark = getCellValueEx(tetmpCell);

                                if (curStructureName.indexOf("出入口") > -1 || curStructureName.indexOf("风亭") > -1)
                                {
                                    tetmpCell = row.getCell(curStructureIndex);
                                    designAmount = getCellValueEx(tetmpCell);//设计数量

                                    tetmpCell = row.getCell(curStructureIndex+1);
                                    underWork = getCellValueEx(tetmpCell);//正在施工

                                    tetmpCell = row.getCell(curStructureIndex+2);//已完全完成
                                    wholeComplete = getCellValueEx(tetmpCell);

                                    tetmpCell = row.getCell(curStructureIndex+3);
                                    cumulativeComplete = getCellValueEx(tetmpCell);//已累计完成
                                }else
                                {
                                    tetmpCell = row.getCell(curStructureIndex);
                                    designAmount = getCellValueEx(tetmpCell);//设计数量

                                    tetmpCell = row.getCell(curStructureIndex+1);
                                    underWork = getCellValueEx(tetmpCell);//正在施工

                                    tetmpCell = row.getCell(curStructureIndex+2);//已完全完成
									cumulativeComplete = getCellValueEx(tetmpCell);
                                }

                                rowMap.put("designAmount",designAmount);
                                rowMap.put("underWork",underWork);
                                rowMap.put("wholeComplete",wholeComplete);
                                rowMap.put("cumulativeComplete",cumulativeComplete);
                                rowMap.put("remark",remark);
                                resultList.add(rowMap);
                            }
                            break;
                        }
                    }
                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            return null;
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        } catch (InvalidFormatException e) {
            e.printStackTrace();
            return null;
        }
        return resultList;
    }

	/**
	 * 获取单元值
	 * @param cell
	 * @return
	 */
	public static String getCellValueEx(Cell cell) {
		if (cell == null)
			return  null;
		int cellType = cell.getCellType();
		String c = null;
		if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
			c = String.valueOf(cell.getNumericCellValue());
		} else if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) {
			c = String.valueOf(cell.getBooleanCellValue());
		}else if(cellType==Cell.CELL_TYPE_FORMULA){ //表达式类型
			try {
				c = String.valueOf(cell.getNumericCellValue());
			} catch (IllegalStateException e) {
				try {
					c = String.valueOf(cell.getRichStringCellValue());
				}catch (Exception e1) {
					return null;
				}
			}
		}else {
			c = cell.getStringCellValue();
		}

		return c.trim();
	}

	/**
	 * 导出Excel（隐患信息）
	 * @param sheetName sheet名称
	 * @param title 标题
	 * @param wb HSSFWorkbook对象
	 * @return
	 */
	public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,List<Map<String,Object>> list, HSSFWorkbook wb){
		HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
		// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
		if(wb == null){
			wb = new HSSFWorkbook();
		}

		// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet(sheetName);

		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
		HSSFRow row = sheet.createRow(0);
		row.setHeight((short) 650);
		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		//声明列对象
		HSSFCell cell = null;

		//创建标题
		for(int i=0;i<title.length;i++){
			sheet.setColumnWidth(i, 6000);
			cell = row.createCell(i);
			cell.setCellValue(title[i]);
			HSSFFont font = wb.createFont();
			font.setFontName("黑体");
			font.setFontHeightInPoints((short) 12);//设置字体大小
			style.setFont(font);
			cell.setCellStyle(style);
		}
		BufferedImage bufferImg = null;//隐患图片
		BufferedImage bufferImg1 = null;//整改图片
		try {
			//创建内容
			HSSFCellStyle styleCon = wb.createCellStyle();
			styleCon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
			styleCon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			for(int i=0;i<list.size();i++){
				row = sheet.createRow(i + 1);
				row.setHeight((short) 2500);
				Map<String,Object> mapTemp = list.get(i);
				//将内容按顺序赋给对应的列对象
				ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
				ByteArrayOutputStream byteArrayOut1 = new ByteArrayOutputStream();
				HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
				//将图片1读到BufferedImage
				String hiddenPath = mapTemp.get("path")==null?"":mapTemp.get("path").toString().replace("null","");
				String attachName = mapTemp.get("attachName")==null?"":mapTemp.get("attachName").toString().replace("null","");
				if(hiddenPath.length() > 0 && attachName.length() > 0){
					String qrcode = PropertiesUtil.get("uploadPath") + File.separator + hiddenPath + File.separator + attachName;
//					qrcode = request.getSession().getServletContext().getRealPath(qrcode);
					if (new File(qrcode).exists()) {
						bufferImg = ImageIO.read(new File(qrcode));
						ImageIO.write(bufferImg, "png", byteArrayOut);
						//图片一导出到单元格B6中
						HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 1013, 245,
								(short) 5, i+1, (short) 5, i+1);
						// 插入图片
						patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
								.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
					}
				}

				//将图片2读到BufferedImage
				String rectifyPath = mapTemp.get("rectifyPath")==null?"":mapTemp.get("rectifyPath").toString().replace("null","");
				String rectifyAttachName = mapTemp.get("rectifyAttachName")==null?"":mapTemp.get("rectifyAttachName").toString().replace("null","");
				if(rectifyPath.length() > 0 && rectifyAttachName.length() > 0){
					String qrcode = PropertiesUtil.get("uploadPath") + File.separator + rectifyPath + File.separator + rectifyAttachName;
//					qrcode = request.getSession().getServletContext().getRealPath(qrcode);
					if (new File(qrcode).exists()) {
						bufferImg1 = ImageIO.read(new File(qrcode));
						ImageIO.write(bufferImg1, "png", byteArrayOut1);
						//图片一导出到单元格B12中
						HSSFClientAnchor anchor1 = new HSSFClientAnchor(10, 10, 1013, 245,
								(short) 11, i+1, (short) 11, i+1);
						// 插入图片
						patriarch.createPicture(anchor1, wb.addPicture(byteArrayOut1
								.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
					}
				}

				cell = row.createCell(0);cell.setCellValue(mapTemp.get("theme")==null?null:mapTemp.get("theme").toString());
				cell.setCellStyle(styleCon);
				cell = row.createCell(2);cell.setCellValue(mapTemp.get("siteName")==null?null:mapTemp.get("siteName").toString());
				cell.setCellStyle(styleCon);
				cell = row.createCell(1);cell.setCellValue(mapTemp.get("bidName")==null?null:mapTemp.get("bidName").toString());
				cell.setCellStyle(styleCon);
				cell = row.createCell(7);cell.setCellValue(mapTemp.get("reportPersonName")==null?null:mapTemp.get("reportPersonName").toString());
				cell.setCellStyle(styleCon);
				cell = row.createCell(9);cell.setCellValue(mapTemp.get("rectifyPersonName")==null?null:mapTemp.get("rectifyPersonName").toString());
				cell.setCellStyle(styleCon);
				cell = row.createCell(10);cell.setCellValue(mapTemp.get("rectifyTime")==null?null:mapTemp.get("rectifyTime").toString());
				cell.setCellStyle(styleCon);
				cell = row.createCell(3);cell.setCellValue(mapTemp.get("rectifyDeadline")==null?null:mapTemp.get("rectifyDeadline").toString());
				cell.setCellStyle(styleCon);
				cell = row.createCell(4);cell.setCellValue(mapTemp.get("findTime")==null?null:mapTemp.get("findTime").toString());
				cell.setCellStyle(styleCon);
				cell = row.createCell(6);cell.setCellValue(mapTemp.get("description")==null?null:mapTemp.get("description").toString());
				cell.setCellStyle(styleCon);
				cell = row.createCell(8);cell.setCellValue(mapTemp.get("rectifyBeginTime")==null?null:mapTemp.get("rectifyBeginTime").toString());
				cell.setCellStyle(styleCon);
				cell = row.createCell(14);cell.setCellValue(mapTemp.get("rectifyEndTime")==null?null:mapTemp.get("rectifyEndTime").toString());
				cell.setCellStyle(styleCon);
				cell = row.createCell(12);cell.setCellValue(mapTemp.get("rectifyContent")==null?null:mapTemp.get("rectifyContent").toString());
				cell.setCellStyle(styleCon);
				cell = row.createCell(15);cell.setCellValue(mapTemp.get("statusName")==null?null:mapTemp.get("statusName").toString());
				cell.setCellStyle(styleCon);
				cell = row.createCell(13);cell.setCellValue(mapTemp.get("confirmerName")==null?null:mapTemp.get("confirmerName").toString());
				cell.setCellStyle(styleCon);
			}
			return wb;
		} catch (Exception e) {
			// TODO: handle exception
			System.err.println(e.getMessage());
		}
		return wb;
	}

	public static void main(String args[]) throws Exception
	{
		File file = new File("D:\\车站、明挖区间主体施工形象进度（2019.5.11）.xlsx");
		FileInputStream fileInputStream =  new FileInputStream(file);
		List<Map<String, Object>>  result = readFromSiteMainWorkScheduleExcel(file.getName(),fileInputStream,null);
		System.out.println(result.toString());
		//String content = "13391011778,13391011779,13391011780,13391011775";
		//writeToMDExcel(fileName,path,null,"xlsx");
		//readFromMDExcel("美达","d:","xlsx");
		//readFromDDExcel("滴滴","d:","xlsx");
	}
}